﻿create database QuanLyBanChau
on primary
(
    name='QLBC',
    filename ='D:\QLBC.mdf',
    size =2MB,
    maxsize = 50MB,
    filegrowth = 2MB
    
)
log on
(
  name='QLBC_log',
    filename ='D:\QLBC_log.ndf',
    size =2MB,
    maxsize = 50MB,
    filegrowth = 2MB
)
go
use QuanLyBanChau
go
CREATE TABLE Chau
(
	Machau varchar(10) primary key,
	Tenchau nvarchar(50),
	Maloai varchar(10),
	Trigia int,
	Kichthuoc int,
	Soluong int
)
go

create table LoaiChau
(
    Maloai varchar(10) primary key,
    TenLoai nvarchar(50)
)
go

CREATE TABLE PhieuNhap
(
    Maphieunhap varchar(10) primary key,
    Ngaynhap datetime,
    Tongsoluongnhap int,
    Tongtien int
)
go

CREATE TABLE ChiTietPhieuNhap
(
    Machitietphieunhap varchar(10) primary key,
    Maphieunhap varchar(10),
    MaChau varchar(10),
    Soluong int,
    Thanhtien int
)
go

create table HoaDon
(
    Mahoadon varchar(10) primary key,
    Makhachhang varchar(10),
    Ngayxuathoadon datetime,
    Tongtien int
)
go

create table ChiTietHoaDon
(
    Machitiethoadon varchar(10) primary key,
    Mahoadon varchar(10),
    Machau varchar(10), 
    Soluong int,
    Thanhtien int
)
go

create table KhachHang
(
    Makhachhang varchar(10) primary key,
    Ten nvarchar(50),
    Diachi nvarchar (100),
    Maloaikhachhang varchar(10) 
)
go

create table LoaiKhachHang
(
    Maloaikhachhang varchar(10) primary key,
    Tenloaikhachhang nvarchar(50)
)
go

create table NhanVien
(
    Username varchar(30) primary key,
    Pass varchar(30),
    Tennv nvarchar(100),
    Loainhanvien int
)
go




alter table Chau
add constraint fk_Chau_LoaiChau foreign key(MaLoai)
references LoaiChau(MaLoai)
go

alter table KhachHang
add constraint fk_KhachHang_LoaiKhachHang foreign key(Maloaikhachhang)
references LoaiKhachHang(Maloaikhachhang)
go

alter table ChiTietPhieuNhap
add constraint fk_ChiTietPhieuNhap_PhieuNhap foreign key(Maphieunhap)
references PhieuNhap(Maphieunhap)
go

alter table ChiTietPhieuNhap
add constraint fk_ChiTietPhieuNhap_Chau foreign key(Machau)
references Chau(Machau)
go

alter table ChiTietHoaDon
add constraint fk_ChiTietHoaDon_HoaDon foreign key(Mahoadon)
references HoaDon(Mahoadon)
go

alter table ChiTietHoaDon
add constraint fk_ChiTietHoaDon_Chau foreign key(Machau)
references Chau(Machau)
go

alter table HoaDon
add constraint fk_HoaDon_KhachHang foreign key(Makhachhang)
references KhachHang(Makhachhang)
go


--INSERT DATA

INSERT INTO LoaiChau VALUES ('CT', N'Chậu Tròn')
INSERT INTO LoaiChau VALUES ('CV', N'Chậu Vuông')
INSERT INTO LoaiChau VALUES ('CO', N'Chậu Oval')
INSERT INTO LoaiChau VALUES ('CCN', N'Chậu Chữ Nhật')
go

INSERT INTO Chau VALUES ('CT30', N'Chậu Tròn 3 Tất', 'CT', 30000,30,5)
INSERT INTO Chau VALUES ('CT40', N'Chậu Tròn 4 Tất', 'CT', 40000,40,3)
INSERT INTO Chau VALUES ('CT50', N'Chậu Tròn 5 Tất', 'CT', 50000,50,8)
INSERT INTO Chau VALUES ('CV30', N'Chậu Vuông 3 Tất', 'CV', 30000,30,5)
INSERT INTO Chau VALUES ('CV40', N'Chậu Vuông 4 Tất', 'CV', 40000,40,3)
INSERT INTO Chau VALUES ('CV50', N'Chậu Vuông 5 Tất', 'CV', 50000,50,8)
INSERT INTO Chau VALUES ('CO30', N'Chậu Oval 3 Tất', 'CO', 50000,30,9)
INSERT INTO Chau VALUES ('CO40', N'Chậu Oval 4 Tất', 'CO', 70000,40,7)
INSERT INTO Chau VALUES ('CN50', N'Chậu Chữ Nhật 30x50', 'CCN', 80000,50,10)
go

INSERT INTO PhieuNhap VALUES('PN01','6/7/2012',100,3000000)
INSERT INTO PhieuNhap VALUES('PN02','6/7/2012',200,5000000)
go

INSERT INTO ChiTietPhieuNhap VALUES('CTPN01','PN01','CT30',20,600000)
INSERT INTO ChiTietPhieuNhap VALUES('CTPN02','PN01','CT40',10,400000)
INSERT INTO ChiTietPhieuNhap VALUES('CTPN03','PN01','CT50',40,2000000)
INSERT INTO ChiTietPhieuNhap VALUES('CTPN04','PN02','CV50',100,5000000)
go

INSERT INTO LoaiKhachHang VALUES ('ML', N'Mua lẻ')
INSERT INTO LoaiKhachHang VALUES ('KQ', N'Khách Quen')
INSERT INTO LoaiKhachHang VALUES ('MS', N'Mua Sĩ')
go

INSERT INTO KhachHang VALUES ('KH1', N'Nguyễn Thị Hoa', 'Hồ Chí Minh','ML') 
INSERT INTO KhachHang VALUES ('KH2', N'Hồ Nga', 'Hồ Chí Minh','MS') 
INSERT INTO KhachHang VALUES ('KH3', N'Nguyễn Tuấn', 'Hà Nội','KQ') 
INSERT INTO KhachHang VALUES ('KH4', N'Trần Nguyệt Ánh', 'Vinh','ML')
INSERT INTO KhachHang VALUES ('KH5', N'Nguyễn Thị Hậu', 'Huế','KQ')
INSERT INTO KhachHang VALUES ('KH6', N'Trần Công', 'Hà Nội','KQ')
INSERT INTO KhachHang VALUES ('KH7', N'Lại Như', 'Đà Năng','MS')
INSERT INTO KhachHang VALUES ('KH8', N'Nguyễn Sang', 'Đà Năng','ML') 
INSERT INTO KhachHang VALUES ('KH9', N'Nguyễn Như Tuyết', 'Vinh','MS')
go

INSERT INTO HoaDon VALUES ('HD01', 'KH5', '2/3/2011', 100000)
INSERT INTO HoaDon VALUES ('HD02', 'KH1', '3/4/2011', 70000)
go

INSERT INTO ChiTietHoaDon VALUES ('CTHD01', 'HD01','CT50', 2, 100000)
INSERT INTO ChiTietHoaDon VALUES ('CTHD02', 'HD02','CT30', 1, 30000)
INSERT INTO ChiTietHoaDon VALUES ('CTHD03', 'HD02','CT40', 1, 40000)
go
INSERT INTO NhanVien VALUES ('admin', '12345', N'Nguyễn Văn A', 1)
go

--Bảng Nhanvien

-- create procedure Get_Nhanvien
-- as
-- begin
--   Select Username , Pass ,Tennv , Loainhanvien from Nhanvien
-- end
-- go
 
 
--  create procedure insert_Nhanvien
--  @Username varchar(30) , @Pass varchar(30) ,@Tennv nvarchar(100) ,@Loainhanvien int
--  as
-- begin
--   insert into Nhanvien values ( @Username , @Pass ,@Tennv ,@Loainhanvien )
-- end
-- go
 
 
--  create procedure Delete_Nhanvien
--  @Username varchar(30)
-- as
-- begin
--   delete Nhanvien where Username = @Username
-- end
-- go
 
-- create procedure Update_Nhanvien

--  @Username varchar(30) , @Pass varchar(30) ,@Tennv nvarchar(100) ,@Loainhanvien int
--as
--begin

--  update Nhanvien
--  set Username = @Username , Pass = @Pass , Tennv = @Tennv , LoaiNhanVien = @Loainhanvien
--  where   Username = @Username
--end
--go

-- Bảng chậu
create procedure Get_Chau
as
begin
   Select Machau, Tenchau, Maloai, Trigia, Kichthuoc, Soluong
   from Chau
end
go
--exec Get_Chau

create procedure insert_Chau
  @Machau varchar(10),
  @Tenchau nvarchar(100),
  @Maloai varchar(10),
  @Trigia int,
  @Kichthuoc int,
  @Soluong int
as
begin
   insert into Chau
   values(@Machau, @Tenchau, @Maloai, @Trigia, @Kichthuoc, @Soluong)
end
go
--exec insert_Chau 'T01', 'Test 01', 'CV', 100000, 50, 0

create procedure Delete_Chau
  @Machau varchar(10)
as
begin
	delete Chau where Machau = @Machau
end
go

create procedure Update_Chau
	@Machau varchar(10),
	@Tenchau nvarchar(100),
	@Maloai varchar(10),
	@Trigia int,
	@Kichthuoc int,
	@Soluong int
as
begin
  update Chau
  set
	Tenchau = @Tenchau,
	Maloai = @Maloai,
	Trigia=@Trigia,
	Kichthuoc = @Kichthuoc,
	Soluong = @Soluong
  where Machau =  @Machau
end
go

--Bảng ChiTietHoaDon

-- create procedure Get_ChiTietHoaDon
-- as
-- begin
--   Select Machitiethoadon , Machau ,Mahoadon ,Soluongban from ChiTietHoaDon
-- end
-- go
 
 
--  create procedure insert_ChiTietHoaDon
--  @Machitiethoadon varchar(10) , @Machau varchar(10) ,@Mahoadon varchar(10) ,@Soluongban int
--  as
-- begin
--   insert into ChiTietHoaDon values ( @Machitiethoadon , @Machau ,@Mahoadon ,@Soluongban )
-- end
-- go
 
 
--  create procedure Delete_ChiTietHoaDon
--  @Machitiethoadon varchar(10)
-- as
-- begin
--   delete ChiTietHoaDon where Machitiethoadon = @Machitiethoadon
-- end
-- go
 
 
 
--  create procedure Update_ChiTietHoaDon
--    @Machitiethoadon varchar(10) , @Machau varchar(10) ,@Mahoadon varchar(10) ,@Soluongban int
-- as
-- begin
--  update ChiTietHoaDon set Machitiethoadon = @Machitiethoadon ,Machau= @Machau ,Mahoadon = @Mahoadon ,Soluongban = @Soluongban
--  where Machitiethoadon = @Machitiethoadon
-- end
-- go
----Bảng HoaDon

-- create procedure Get_HoaDon
-- as
-- begin
--  Select Mahoadon , Makhachhang , Ngayxuathoadon from HoaDon
-- end
-- go
 
 
--  create procedure insert_HoaDon
--  @Mahoadon varchar(10), @Makhachhang varchar(10), @Ngayxuathoadon datetime
-- as
-- begin
--  insert into HoaDon VALUES (@Mahoadon , @Makhachhang , @Ngayxuathoadon) 
-- end
-- go
 
 
--  create procedure Delete_HoaDon
--   @Mahoadon varchar(10)
-- as
-- begin
--   delete HoaDon where Mahoadon = @Mahoadon
-- end
-- go
 
 
 
--  create procedure Update_HoaDon
--    @Mahoadon varchar(10), @Makhachhang varchar(10), @Ngayxuathoadon datetime
-- as
-- begin
--   update HoaDon set Mahoadon = @Mahoadon ,Makhachhang = @Makhachhang ,Ngayxuathoadon = @Ngayxuathoadon
--   where Mahoadon = @Mahoadon
-- end
-- go
----Bảng KhachHang

-- create procedure Get_KhachHang
-- as
-- begin
--  select Makhachhang , Ten ,Diachi ,Maloaikhachhang  from  KhachHang
-- end
-- go
 
 
--  create procedure insert_KhachHang
--  @Makhachhang varchar(10) , @Ten nvarchar(100),@Diachi nvarchar(100) ,@Maloaikhachhang varchar(10)
-- as
-- begin
--   insert into KhachHang VALUES (@Makhachhang , @Ten ,@Diachi ,@Maloaikhachhang)
-- end
-- go
 
 
--  create procedure Update_KhachHang
--  @Makhachhang varchar(10) , @Ten nvarchar(100),@Diachi nvarchar(100) ,@Maloaikhachhang varchar(10)
-- as
-- begin
--  Update KhachHang set Makhachhang = @Makhachhang ,Ten = @Ten ,Diachi = @Diachi 
-- end
-- go
 
 
 
--  create procedure Delete_KhachHang
--  @Makhachhang varchar(10)
-- as
-- begin
--   delete KhachHang where Makhachhang = @Makhachhang
-- end
-- go
--Bảng LoaiChau

 create procedure Get_LoaiChau
 as
 begin
   select Maloai, TenLoai from LoaiChau
 end
 go 
 
create procedure insert_LoaiChau
   @Maloai varchar(10), @Tenloai nvarchar(100)
 as
 begin
  insert into LoaiChau values(@Maloai, @Tenloai)
 end
 go
 --exec insert_LoaiChau 'Test', 'Test Name'
 
  create procedure Delete_LoaiChau
   @Maloai varchar(10)
 as
 begin
   Delete LoaiChau where Maloai = @Maloai
 end
 go 
 
  create procedure Update_LoaiChau
     @Maloai varchar(10), @TenLoai nvarchar(100)
 as
 begin
   update LoaiChau set Maloai = @Maloai, TenLoai = @TenLoai
 end
 go
 
 
--Bảng LoaiKhachHang
 --create procedure Get_LoaiKhachHang
 --as
 --begin
 --  Select Maloaikhachhang , Ten from LoaiKhachHang
 --end
 --go
 
 
 -- create procedure insert_LoaiKhachHang
 -- @Maloaikhachhang varchar(10), @Ten nvarchar(100)
 --as
 --begin
 -- insert into LoaiKhachHang values (@Maloaikhachhang , @Ten)
 --end
 --go
 
 
 -- create procedure Update_LoaiKhachHang
 --   @Maloaikhachhang varchar(10), @Ten nvarchar(100)
 --as
 --begin
 --  update LoaiKhachHang set Maloaikhachhang =  @Maloaikhachhang , Ten = @Ten
 --end
 --go
 
 
 
 -- create procedure Delete_LoaiKhachHang
 --  @Maloaikhachhang varchar(10)
 --as
 --begin
 --  delete LoaiKhachHang where Maloaikhachhang =  @Maloaikhachhang 
 --end
 --go
--Bảng PhieuNhap
 create procedure Get_PhieuNhap
 as
 begin
  select Maphieunhap ,Machau ,Ngaynhap , Soluongnhap  from PhieuNhap
 end
 go
 
 
  create procedure insert_PhieuNhap
  @Maphieunhap varchar(10),@Machau varchar(10),@Ngaynhap datetime, @Soluongnhap int
 as
 begin
  insert into PhieuNhap values (@Maphieunhap ,@Machau ,@Ngaynhap , @Soluongnhap)
 end
 go
 
 
  create procedure Delete_PhieuNhap
  @Maphieunhap varchar(10)
 as
 begin
  Delete PhieuNhap where Maphieunhap = @Maphieunhap
 end
 go
 
 
 
  create procedure Update_PhieuNhap
    @Maphieunhap varchar(10),@Machau varchar(10),@Ngaynhap datetime, @Soluongnhap int
 as
 begin
  update PhieuNhap set Maphieunhap =  @Maphieunhap ,Machau = @Machau ,Ngaynhap =  @Ngaynhap ,Soluongnhap = @Soluongnhap
 end
 go




